package com.chinamsp.dydb.mybatis;

import java.beans.PropertyDescriptor;
import java.lang.annotation.Annotation;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.log4j.Logger;
import org.springframework.beans.BeanUtils;
import org.springframework.util.StringUtils;

import com.chinamsp.dydb.annotation.DBAlias;
import com.chinamsp.dydb.annotation.DynamicMapper;
import com.chinamsp.dydb.annotation.DynamicMapperType;
import com.chinamsp.dydb.convert.ORMNameConverter;
import com.chinamsp.dydb.crud.DeleteAbility;
import com.chinamsp.dydb.util.DBUtils;

/**
 * 根据类名生成BaseDao中定义的Mybatis的映射SQL语句
 * 现在支持动态insert
 * @author shifenghu
 * 
 */
public class MybatisCRUDBuilder {
	
	/**
	 * 日志
	 */
	private static final Logger logger = Logger.getLogger(MybatisCRUDBuilder.class);

	/**
	 * 回车换行
	 */
	private static final String NEW_LINE_BREAK = System.lineSeparator();
	
	
	/**
	 * orm 名称转换执行器
	 */
	private ORMNameConverter ormNameConverter;
	
	public <T> String buildByClass(Class<T> clazz, ORMNameConverter ormNameConverter) {
		this.ormNameConverter = ormNameConverter;
		String build = this.createSqlByEntity(clazz);
		logger.debug(clazz.getName() + " , build is " + build);
		return build;
	}

	/**
	 * 文件路径
	 */
	private static final String TEMPLATE_FILE = "create-sql.txt";

	/**
	 * 合并参数
	 * @param context
	 * @return
	 */
	public String mergeTemplate(Map<String, String> context) {
		String template = DBUtils.readByClasspath(TEMPLATE_FILE, true);
		// 开始替换逐个替换
		for(String key : context.keySet()){
			template = template.replaceAll("\\$\\{" + key + "\\}", context.get(key));
		}
		return template;
	}
	
	/**
	 * 主键列表
	 */
	private List<String> pkList = new ArrayList<String>(2);

	/**
	 * 属性映射
	 */
	private Map<String, String> propertyMappings = new HashMap<String, String>(32);
	
	/**
	 * 功能:初始化类字段属性
	 * 
	 * @param clazz
	 *            要转换的实体类
	 * @param type
	 *            类型 file代表文件，否则代表字符串
	 * @return 如果type=file返回xml文件绝对路径，否则返回生成的xml内容
	 */
	private <T> String createSqlByEntity(Class<T> clazz) {
		String tableName = clazz.getSimpleName();
		// 转换表名
		tableName = ormNameConverter.convertToSchema(tableName);
		// 如果重置了表名
		if(clazz.getAnnotation(DBAlias.class) != null && !StringUtils.isEmpty(clazz.getAnnotation(DBAlias.class).value())){
			tableName = clazz.getAnnotation(DBAlias.class).value();
		}
		pkList.clear();
		PropertyDescriptor[] properties = BeanUtils.getPropertyDescriptors(clazz);
		for(PropertyDescriptor property : properties){
			// 如果没有 get 或 set 方法，则忽略此属性
			if(property.getReadMethod() == null || property.getWriteMethod() == null){
				continue;
			}
			propertyMappings.put(property.getName(), propertyToField(property));
			// 判断是否是主键
			if (property.getName().equalsIgnoreCase("id")){
				pkList.add(ormNameConverter.convertToField(property.getName()));
			}
		}
		String entityClassName = clazz.getName();
		String findByIdSql = findByIdSql(tableName);
		String inserSql = insertSql(tableName);
		String updateSql = updateSql(tableName);
		Map<String, String> context = new HashMap<String, String>(32);
		String deleteSql;
		String deleteByIdsSql;
		if (DeleteAbility.class.isAssignableFrom(clazz)) {
			deleteSql = deleteDeletedSql(tableName, false);
			deleteByIdsSql = deleteDeletedSql(tableName, true);
		} else {
			deleteSql = deleteSql(tableName);
			deleteByIdsSql = "DELETE FROM " + tableName + " WHERE id IN <include refid=\"common.idsForEach\"/>";
		}
		context.put("resultMap", resultMap(clazz, properties));
		context.put("deleteById", deleteSql);
		context.put("deleteByIdsSql", deleteByIdsSql);

		context.put("findById", findByIdSql);

		context.put("insert", inserSql);
		context.put("update", updateSql);
		context.put("updateBatchSql", updateBatchSql(tableName));

		context.put("insertBatch", insertBatchSql(tableName));
		
		context.put("tableName", tableName);
		context.put("findFieldValueById", findFieldValueById(tableName));
		context.put("findIdsByIdsSql", findIdsByIdsSql(tableName, clazz));
		context.put("dynamicMapper", dynamicMapper(tableName, clazz));
		context.put("findCountSql", findCountSql(tableName, clazz));
		
		// 把model替换成Dao
		String daoClassName = ormNameConverter.convertToDao(entityClassName);
		context.put("findSql", findSql(tableName, clazz));
		context.put("daoClass", daoClassName);
		context.put("entityClass", entityClassName);
		context.put("cached", "false");
		return mergeTemplate(context);

	}
	
	/**
	 * find 方法动态 mapper 生成
	 * @param tableName
	 * @param clazz
	 * @return
	 */
	private <T> String findSql(String tableName, Class<T> clazz) {

		StringBuilder findSql = new StringBuilder();
		findSql.append("SELECT * FROM ").append(tableName);
		findSql.append(NEW_LINE_BREAK).append("<where>");
		findSql.append(NEW_LINE_BREAK).append(
				"<include refid=\"common.dynamicConditionsNoWhere\"/>");
		if (DeleteAbility.class.isAssignableFrom(clazz)) {
			findSql.append(NEW_LINE_BREAK).append("AND Deleted = 0");
		}
		findSql.append(NEW_LINE_BREAK).append("</where>");
		// 添加 order by
		findSql.append(NEW_LINE_BREAK).append("<include refid=\"common.searchOrder\"/>");
		return findSql.toString();
	}
	
	/**
	 * findIdsByIds 方法动态 mapper 生成
	 * @param tableName
	 * @param clazz
	 * @return
	 */
	private <T> String findIdsByIdsSql(String tableName, Class<T> clazz) {
		StringBuilder findSql = new StringBuilder();
		findSql.append("SELECT id FROM ").append(tableName);
		findSql.append("WHERE id IN ");
		findSql.append("<foreach item=\"id\" collection=\"ids\" open=\"(\" separator=\",\" close=\")\">#{id}</foreach>");
		return findSql.toString();
	}

	private String deleteDeletedSql(String tableName, boolean batch) {
		StringBuilder updateSql = new StringBuilder();
		if (batch) {
			updateSql
					.append("UPDATE ")
					.append(tableName)
					.append(" SET deleted=1 WHERE id IN <include refid=\"common.idsForEach\"/>");
		} else {
			updateSql.append("UPDATE ").append(tableName)
					.append(" SET deleted=1 WHERE id=#{id}");
		}
		return updateSql.toString();
	}

	/**
	 * 功能:生成insert语句
	 * 
	 * @param tableName
	 * @param columns
	 * @return
	 */
	private String insertSql(String tableName) {
		StringBuilder insertSql = new StringBuilder();
		StringBuilder valueSql = new StringBuilder();
		insertSql.append("INSERT INTO ").append(tableName).append("(");
		insertSql.append(" <trim suffix='' suffixOverrides=','>");
		valueSql.append(" <trim suffix='' suffixOverrides=','>");
		// 逐个属性处理
		for (String pro : propertyMappings.keySet()) {
			insertSql.append("<if test=\"" + pro + " != null\" >");
			insertSql.append(propertyMappings.get(pro)).append(  ",");
			insertSql.append("</if>");
			valueSql.append("<if test=\"" + pro + " != null\" >");
			valueSql.append("#{").append(pro).append("}").append( ",") ;
			valueSql.append("</if>");
		}
		valueSql.append("</trim>");
		insertSql.append("</trim>) VALUES (").append(valueSql).append(")");
		return insertSql.toString();
	}
	
	/**
	 * 生成 batch sql语句
	 * @param tableName
	 * @param columns
	 * @return
	 */
	private String insertBatchSql(String tableName){
		StringBuilder insertSql = new StringBuilder();
		insertSql.append("INSERT <if test=\"ignore\">IGNORE</if> INTO ").append(tableName);
		//遍历fields
		insertSql.append("( <trim suffix='' suffixOverrides=','>");
		for(String field : propertyMappings.values()){
			insertSql.append('`' + field + '`').append( ",");
		}
		insertSql.append("</trim> )");
		insertSql.append(" VALUES ");
		//添加遍历
		insertSql.append("<foreach item=\"testbean\" collection=\"beans\" open=\"\" separator=\",\" close=\"\">");
		insertSql.append("( <trim suffix='' suffixOverrides=','>");
		for (String pro : propertyMappings.keySet()) {
			insertSql.append("#{testbean.").append(pro).append("}").append( ",");
		}
		insertSql.append("</trim> )");
		//关闭遍历
		insertSql.append("</foreach>");
		return insertSql.toString();
	}

	/**
	 * 功能:生成update语句
	 * 
	 * @param tableName
	 * @param columns
	 * @return
	 */
	private String updateSql(String tableName) {
		// <set>元素会动态前置 SET关键字,而且也会消除任意无关的逗号
		StringBuilder updateSql = updateFields(tableName, false);
		if (pkList.size() > 0) {
			updateSql.append(pkWhereSqlStr());
		}
		return updateSql.toString();
	}

	/**
	 * 更新字段
	 * @param tableName
	 * @param columns
	 * @param useAlias
	 * @return
	 */
	private StringBuilder updateFields(String tableName, boolean isAlias) {
		StringBuilder updateSql = new StringBuilder();
		updateSql.append("UPDATE ").append(tableName).append(" <set> ");
		String alias;
		for (String pro : propertyMappings.keySet()) {
			alias = isAlias ? "entity." + pro : pro;
			updateSql.append(" <if test=\"").append(alias)
					.append("!= null\"> ");
			updateSql.append(propertyMappings.get(pro)).append("=#{").append(alias).append("},");
			updateSql.append(" </if> ");
		}
		updateSql.append(" </set> ");
		return updateSql;
	}

	private String updateBatchSql(String tableName) {
		StringBuilder updateSql = updateFields(tableName, true);
		updateSql.append(" WHERE id IN ");
		updateSql.append("<include refid=\"common.idsForEach\"/>");
		return updateSql.toString();
	}
	
	/**
	 * 生成 find count sql
	 * @param tableName
	 * @param clazz
	 * @return
	 */
	private <T> String findCountSql(String tableName, Class<T> clazz){
		StringBuilder findSql = new StringBuilder();
		findSql.append("SELECT COUNT(*) FROM ").append(tableName);
		findSql.append(NEW_LINE_BREAK).append("<where>");
		findSql.append(NEW_LINE_BREAK).append(
				"<include refid=\"common.dynamicConditionsNoWhere\"/>");
		if (DeleteAbility.class.isAssignableFrom(clazz)) {
			findSql.append(NEW_LINE_BREAK).append("AND Deleted = 0");
		}
		findSql.append(NEW_LINE_BREAK).append("</where>");
		return findSql.toString();
	}

	/**
	 * 功能:生成findById语句
	 * <p>
	 * 
	 * @param tableName
	 * @return
	 */
	private String findByIdSql(String tableName) {
		StringBuilder findByIdSql = new StringBuilder();
		findByIdSql.append("select * from ").append(tableName);
		findByIdSql.append(pkWhereSqlStr());
		return findByIdSql.toString();
	}

	/**
	 * findFieldValueById
	 * @param tableName
	 * @return
	 */
	private String findFieldValueById(String tableName){
		StringBuilder findByIdSql = new StringBuilder();
		findByIdSql.append("SELECT \\${fieldName} FROM ").append(tableName);
		findByIdSql.append(pkWhereSqlStr());
		return findByIdSql.toString();
	}
	
	/**
	 * 功能:生成delete语句
	 * 
	 * @param tableName
	 * @return
	 */
	private String deleteSql(String tableName) {
		StringBuilder deleteSql = new StringBuilder();
		deleteSql.append("DELETE FROM ").append(tableName);
		deleteSql.append(pkWhereSqlStr());
		return deleteSql.toString();
	}

	/**
	 * 主键where条件拼接
	 * 
	 * @return
	 */
	private String pkWhereSqlStr() {
		if (pkList.size() == 0) {
			return "";
		}
		StringBuilder pkStr = new StringBuilder();
		pkStr.append(" WHERE ");
		for (String pk : pkList) {
			pkStr.append(pk).append("=").append("#{").append(pk.toLowerCase()).append("}").append(" AND ");
		}
		return pkStr.delete(pkStr.length() - 4, pkStr.length()).toString();
	}
	
	/**
	 * 生成动态mapper
	 * @param tableName
	 * @param clazz
	 * @return
	 */
	private String dynamicMapper(String tableName, Class<?> clazz){
		// 获取dao class
		Class<?> daoClass = ormNameConverter.getDaoClass(clazz);
		StringBuffer sb = new StringBuffer();
		// 逐个处理 dynamic mapper 方法
		DynamicMapper  dm;
		Annotation[][] params;
		String field;
		for(Method m : daoClass.getMethods()){
			if((dm = m.getAnnotation(DynamicMapper.class)) != null){
				params = m.getParameterAnnotations();
				// select
				if(dm.type() == DynamicMapperType.Select){
					// 生成 mapp wraper
					sb.append("<select id=\"" + m.getName() + "\"" + (StringUtils.isEmpty(dm.returnType()) ? "resultMap=\"_resultMap\"" : " resultType=\"" + dm.returnType() + "\"") + ">");
					sb.append("SELECT " + dm.value() + "  FROM " + tableName);
					//如果未定制 where
					if(StringUtils.isEmpty(dm.where())){
						sb.append("<where>");
						sb.append("<![CDATA[");
						for(Annotation[] at : params){
							field = findByAnnotations(at);
							sb.append(" AND " + ormNameConverter.convertToField(field) + "=#{" + field + "}");
						}
						sb.append("]]>");
						sb.append("</where>");
					}
					else{
						sb.append(" WHERE " + dm.where());
					}
					sb.append("</select>");
				}
				// 删除
				else if(dm.type() == DynamicMapperType.Delete){
					// 生成 mapp wraper
					sb.append("<delete id=\"" + m.getName() + "\" >");
					sb.append("DELETE FROM " + tableName);
					//如果未定制 where
					if(StringUtils.isEmpty(dm.where())){
						sb.append("<where>");
						sb.append("<![CDATA[");
						for(Annotation[] at : params){
							field = findByAnnotations(at);
							sb.append(" AND " + ormNameConverter.convertToField(field) + "=#{" + field + "}");
						}
						sb.append("]]>");
						sb.append("</where>");
					}
					else{
						sb.append(" WHERE " + dm.where());
					}
					sb.append("</delete>");
				}
				// 更新
				else {
					// 生成 mapp wraper
					sb.append("<update id=\"" + m.getName() + "\" >");
					sb.append("UPDATE " + tableName + " SET " + dm.value() + " ");
					//如果未定制 where
					if(StringUtils.isEmpty(dm.where())){
						sb.append("<where>");
						sb.append("<![CDATA[");
						for(Annotation[] at : params){
							field = findByAnnotations(at);
							sb.append(" AND " + ormNameConverter.convertToField(field) + "=#{" + field + "}");
						}
						sb.append("]]>");
						sb.append("</where>");
					}
					else{
						sb.append(" WHERE " + dm.where());
					}
					sb.append("</update>");
				}
			}
		}
		//logger.debug(clazz.getName() + " dynamic sql is " + sb.toString());
		return sb.toString();
	}
	
	/**
	 * 获取 mybatis param的别名
	 * @param ats
	 * @return
	 */
	private String findByAnnotations(Annotation[] ats){
		for(Annotation at : ats){
			if(at.annotationType() == Param.class){
				return ((Param) at).value();
			}
		}
		return "id";
	}
	
	
	/**
	 * 生成 result type
	 * @param clazz
	 * @param properties
	 * @return
	 */
	private String resultMap(Class<?> clazz, PropertyDescriptor[] properties){
		StringBuffer sb = new StringBuffer("<resultMap type=\"" + clazz.getName() + "\" id=\"_resultMap\">");
		// 逐个属性 遍历，如果是需要 隐射的则添加到隐射表
		DBAlias alias;
		for(PropertyDescriptor pd : properties){
			alias = pd.getReadMethod().getAnnotation(DBAlias.class);
			//如果存在别名，则添加属性映射
			if(alias != null && !StringUtils.isEmpty(alias.value())){
				sb.append("<result column=\"" + alias.value() + "\" property=\"" + pd.getName() + "\" />");
			}
		}
		sb.append("</resultMap>");
		return sb.toString();
	}
	
	
	/**
	 * 根据属性获取字段名，并进行下划线转换
	 * @param property
	 * @return
	 */
	private String propertyToField(PropertyDescriptor property){
		// 检查 是否有手动映射
		DBAlias alias = property.getReadMethod().getAnnotation(DBAlias.class);
		// 如果没有别名则，直接转换
		if(alias == null){
			return ormNameConverter.convertToField(property.getName());
		}
		// 如果有别名，则直接根据别名走
		return alias.value();
	}
	
	
}
